﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using MyKTVDao;
using System.Data;
namespace MeadiePlayer
{
   public class SongInfoDao : BaseDao
    {

        /// <summary>
        /// 按照sql语句 然后查询结果 返回
        /// List<SongInfoEntity>集合
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public List<SongInfoEntity> QuerySongListBySql(string sql, SqlParameter[] param) {
            List<SongInfoEntity> list = new List<SongInfoEntity>();
            //获得打开的数据库连接
            using (SqlConnection conn = this.CreateOpenSqlConnection())
            {
                //获得command对象
                SqlCommand command = new SqlCommand(sql, conn);
                //使用命令化参数防止sql注入
                if(param!=null)
                command.Parameters.AddRange(param);
                //获得datareader对象
                //使用CommandBehavior.CloseConnection 让系统资管关闭reader对象
                SqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection);
                //使用datareader对象去去读取数据 存入dic字典中
                while (reader.Read())
                {
                    SongInfoEntity entity = new SongInfoEntity();
                    //通过read读取列名 然后封装到实体类中
                    entity.Id = Convert.ToInt32(reader["song_id"]);
                    entity.Name = reader["song_ab"].ToString();
                    entity.PlayCount = Convert.ToInt32(reader["song_word_count"]);
                    entity.SingerId = Convert.ToInt32(reader["songtype_id"]);
                    entity.SongTypeId = Convert.ToInt32(reader["singer_id"]);
                    entity.SongUrl = reader["song_url"].ToString();
                    entity.EnName = reader["song_play_count"].ToString();
             
                    //将对象添加到集合中
                    list.Add(entity);
                }
                //返回结果
                return list;
            }

        }
    }
}
